package com.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.utils.JDBCUtils;
import com.bean.Passage;
import com.bean.User;
import com.mysql.jdbc.ResultSetMetaData;

public class UserDao {
	
	//用于登录查询
    //如果返回0表示存在相应用户且密码正确
    //如果返回1表示密码错误
    //如果返回2表示不存在该用户名
    public int login(String email,String password)
    		throws Exception,ClassNotFoundException, SQLException,
            IllegalAccessException, InstantiationException
    {
    	Connection conn = null;
    	Statement stmt = null;
    	ResultSet rs = null;
    	String getEmail,getPassword;
		conn = JDBCUtils.getConnection();
		stmt = conn.createStatement();
		String sql="select email,password from customer";
		rs = stmt.executeQuery(sql);
		while(rs!=null&&rs.next())
	        {
	            getEmail=rs.getString("email");
	            getPassword=rs.getString("password");
	            if(getEmail.equals(email)) {
	                if (getPassword.equals(password)) {
	                	stmt.close();
	                    conn.close();
	                    return 0;
	                }
	                else
	                {
	                	stmt.close();
	                    conn.close();
	                    return 1;
	
	                }
	            }
	        }
		    stmt.close();
	        conn.close();
	        return 2;
    }
    
    //查询指定邮箱的用户信息
    public User queryID(String email)
    		throws Exception,ClassNotFoundException, SQLException,
            IllegalAccessException, InstantiationException
    {
    	User user = new User();
    	Connection conn = null;
    	Statement stmt = null;
    	ResultSet rs = null;
		conn = JDBCUtils.getConnection();
		stmt = conn.createStatement();
		String sql = "SELECT * FROM customer WHERE email = '" + email +"';";
		rs = stmt.executeQuery(sql);
		if(rs != null && rs.next()) {
			user.setUsername(rs.getInt("ID"));
            user.setName(rs.getString("name"));
            user.setSex(rs.getString("sex"));
            user.setEmail(rs.getString("email"));
            user.setAge(rs.getInt("age"));
            user.setIntroduction(rs.getString("introduction"));
        }
		return user;
    }
    
    //用于新用户注册
    //如果返回0表示注册成功
    //如果返回1表示ID已经存在
    //如果返回2表示用户名已经存在
    public int register(String email,String name,String password)
    		throws Exception,ClassNotFoundException, SQLException,
            IllegalAccessException, InstantiationException
    {
    	Connection conn = null;
    	Statement stmt = null;
    	ResultSet rs = null;
		conn = JDBCUtils.getConnection();
		stmt = conn.createStatement();
		String sql="select email,name from customer ";
		rs = stmt.executeQuery(sql);
		String getEmail;
        String getName;
        boolean isExist = false;
        while(rs!=null&&rs.next())
        {
            getName = rs.getString("name");
            getEmail = rs.getString("email");
            if (getEmail.equals(email)) {
                return 1;
            }
            if (getName.equals(name)) {
                return 2;
            }
        }
        if(isExist == false)
        {
        	String sql1="INSERT INTO customer(email,name,password) VALUES('"
                    + email + "','" + name + "','" + password + "')";
        	stmt.execute(sql1);
            return 0;
        }
        return 3;
    	
    }
    
    
  //根据ID获取customer表中信息
    public User searchUserInfo(String ID,User user) 
    		throws Exception,ClassNotFoundException,
            IllegalAccessException, InstantiationException, SQLException
    {
    	Connection conn = null;
    	Statement stmt = null;
    	ResultSet rs = null;
		conn = JDBCUtils.getConnection();
		stmt = conn.createStatement();
		String sql = "SELECT * FROM customer WHERE ID = '" + ID +"';";
		rs = stmt.executeQuery(sql);
//		
//		ResultSetMetaData rsmd=(ResultSetMetaData) rs.getMetaData();  //获取结果集的元数据
//		int columns=rsmd.getColumnCount();  //获取结果集的列数
//		while(rs.next()){
//			for(int i=1;i<=columns;i++){
//				System.out.print(rs.getString(i)+"\t" + "rs结果");					
//			}				
//			System.out.println();
//		}
		if(rs != null && rs.next()) {
			user.setUsername(rs.getInt("ID"));
            user.setName(rs.getString("name"));
            user.setSex(rs.getString("sex"));
            user.setEmail(rs.getString("email"));
            user.setAge(rs.getInt("age"));
            user.setIntroduction(rs.getString("introduction"));
            

        }
		
        rs.close();
        stmt.close();
        conn.close();
        System.out.println(user.getEmail()+"UserDao");
        System.out.println(user.getAge()+"UserDao");
        System.out.println(user.getIntroduction()+"UserDao");
        System.out.println(user.getUsername()+"UserDao");
        return user;
    }
    
    
    
    
    
    //用于删除游记
      public int deletePassage(int passageID) 
      		throws Exception,ClassNotFoundException,
              IllegalAccessException, InstantiationException, SQLException 
      {
    	  Connection conn = null;
          Statement stmt = null;
          ResultSet rs = null;
  		  conn = JDBCUtils.getConnection();
  		  stmt = conn.createStatement();
  	      String sql = "DELETE from passage where passageID = '" + passageID +"';";
  		  stmt.execute(sql);
  		  return 0;
      }
    
    
    
    //根据用户ID查询该用户的所有passage数量
      public int searchPassageNumberFromAdmin(int ID) 
    		  throws Exception,ClassNotFoundException, IllegalAccessException
      , InstantiationException, SQLException 
      {
    	  Connection conn = null;
          Statement stmt = null;
          ResultSet rs = null;
  		  conn = JDBCUtils.getConnection();
  		  stmt = conn.createStatement();
  		  String sql = "SELECT COUNT(*) AS number FROM passage where authorID='" + ID +"';";
  	 	  rs = stmt.executeQuery(sql);
  		  if(rs.next()) {
            int result=rs.getInt("number");
            stmt.close();
            conn.close();
            return result;
          } 
  		  else {
            stmt.close();
            conn.close();
            return 0;
          }
      }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
  //用于修改用户信息
    public void adminEdit(String ID,String email,String name,String sex,int age,String introduction) 
    		throws Exception,ClassNotFoundException,
            IllegalAccessException, InstantiationException, SQLException 
    {
    	Connection conn = null;
    	Statement stmt = null;
    	ResultSet rs = null;
		conn = JDBCUtils.getConnection();
		stmt = conn.createStatement();
		String sql = "UPDATE customer SET name='" + name +"',sex='"
		        + sex + "',age=" + age + ",email='" + email + "',introduction='"
		                + introduction + "' WHERE ID ='" + ID + "'";
		stmt.execute(sql);
    }
    
    
  //建立分享连接
    public int share(String shareID,String shareCity,String shareTitle,String shareContent,int shareCost,int shareDays,String shareTime)
    		throws Exception,ClassNotFoundException, SQLException,
            IllegalAccessException, InstantiationException
    {
    	Connection conn = null;
    	Statement stmt = null;
    	ResultSet rs = null;
		conn = JDBCUtils.getConnection();
		stmt = conn.createStatement();
        String sql = "SELECT * FROM customer where ID ='"+shareID+"'";
        rs = stmt.executeQuery(sql);
        String getAuthorName;
        String getAuthorID;
        while(rs!=null&&rs.next()){

            getAuthorName=rs.getString("name");
            getAuthorID=rs.getString("ID");
            stmt.execute(sql);


            String insertSQL="INSERT INTO passage(authorID,title,passageContent,cost,days,authorName,passageDate,city) VALUES" +
                    "('" + getAuthorID + "','" + shareTitle + "','"+ shareContent + "','"+ shareCost + "','"+ shareDays + "','"+ getAuthorName + "','"+ shareTime + "','"+ shareCity + "');";

            stmt.execute(insertSQL);

            return 0;
        }
        rs.close();
        stmt.close();
        conn.close();
        return 1;
    }
    
    //用于返回passage的编号
    //如果返回0，发生错误
    public int returnPassageID(String shareTitle)
    		throws Exception,ClassNotFoundException, SQLException,
            IllegalAccessException, InstantiationException
    {
    	Connection conn = null;
    	Statement stmt = null;
    	ResultSet rs = null;
		conn = JDBCUtils.getConnection();
		stmt = conn.createStatement();
        String sql = "SELECT passageID FROM passage where title ='"+shareTitle+"'";
        rs = stmt.executeQuery(sql);
        int getPassageID = 0;
        if(rs!=null&&rs.next()){

            getPassageID=rs.getInt("passageID");
            stmt.execute(sql);
        }
        return getPassageID;
    }
    
  //按照time的值返回passage列表
    public Passage searchAdminPassage(Passage passage,int ID,int time) throws Exception,ClassNotFoundException,
            IllegalAccessException, InstantiationException, SQLException {

    	Connection conn = null;
    	Statement stmt = null;
    	ResultSet rs = null;
		conn = JDBCUtils.getConnection();
		stmt = conn.createStatement();
        
		String sql = "SELECT * FROM passage where authorID ='"+ID+"'";
        
		rs = stmt.executeQuery(sql);

        for(int i=1;i<time;i++) {
            if (rs != null)
                rs.next();
        }
        if(rs != null && rs.next()) {
            passage.setAuthorID(rs.getString("authorID"));
            String passageID = rs.getString("passageID");
            passage.setPassageID(Integer.valueOf(passageID).intValue());
            passage.setTitle(rs.getString("title"));
            passage.setLike(rs.getInt("likeNum"));
            passage.setCost(rs.getInt("cost"));
            passage.setDays(rs.getInt("days"));
            passage.setAuthorName(rs.getString("authorName"));
            passage.setDate(rs.getString("passageDate"));
            passage.setCity(rs.getString("city"));
        }

        /*result.close();
        stmt.close();
        conn.close();*/
        try {
        	if(rs != null){
        		rs.close();
        	}
        	if(stmt != null){
        		stmt.close();
        	}
        	if(conn != null){
        	conn.close();
        	}
        	} catch (Exception e) {
        	e.printStackTrace();
        	}
        return passage;
    }
    
    

}
